Exploratory Analysis - Miovision Counts

Aakash Harpalani
Thursday, December 14, 2017

Setup

Loading libraries:

library(RPostgreSQL)
library(ggplot2)
library(lubridate)
library(dplyr)

Establishing connections and credentials:

drv <- dbDriver("PostgreSQL")
source("connect/connect.R")

Analysis

Set intersection(s) of interest:

intersection_ids = seq(1,31,1)

Retrieve 15-min count data:

strSQL <-
  paste0("SELECT datetime_bin, intersection_name, classification, leg, dir, volume ",
         "FROM miovision.volumes_15min ",
         "INNER JOIN miovision.classifications USING (classification_uid) ",
         "INNER JOIN miovision.intersections USING (intersection_uid) ",
         "ORDER BY datetime_bin, intersection_name, classification_uid, leg, dir ")
data <- dbGetQuery(con, strSQL)

Overall volumes, by date/tod

all_vehicles <- data %>%
  group_by(datetime_bin, intersection_name, leg, dir) %>%
  filter(classification %in% c('Lights','Single-Unit Trucks','Articulated Trucks','Buses')) %>%
  summarize(total_volume = sum(volume))
head(all_vehicles)

## Source: local data frame [6 x 5]
## Groups: datetime_bin, intersection_name, leg [3]
## 
##          datetime_bin intersection_name   leg   dir total_volume
##                <dttm>             <chr> <chr> <chr>        <int>
## 1 2017-10-03 13:15:00   King / Bathurst     E    EB           81
## 2 2017-10-03 13:15:00   King / Bathurst     E    WB           74
## 3 2017-10-03 13:15:00   King / Bathurst     N    NB          142
## 4 2017-10-03 13:15:00   King / Bathurst     N    SB          118
## 5 2017-10-03 13:15:00   King / Bathurst     S    NB          133
## 6 2017-10-03 13:15:00   King / Bathurst     S    SB          106

all_vehicles$dt <- floor_date(all_vehicles$datetime_bin,'1 day')
all_vehicles$time_bin <- as.POSIXct(paste(sprintf("%02d",hour(all_vehicles$datetime_bin)),sprintf("%02d",minute(all_vehicles$datetime_bin)),sep=":"), format = "%H:%M")
all_vehicles$wk <- strftime(all_vehicles$dt,format='%W')
all_vehicles$dow <- strftime(all_vehicles$dt, format = '%u')

Plot all direction/leg combinations (Adelaide and Bathurst):

for (intersection_id in intersection_ids){
  
  strSQL <-
    paste0("SELECT intersection_name FROM miovision.intersections WHERE intersection_uid = ",intersection_id)
  intersection_name <- as.character(dbGetQuery(con, strSQL))

  print(paste0(intersection_id,': ', intersection_name))
  
  for (leg in c('N','S','W','E')){
    for (dir in c('NB','SB','EB','WB')){
      if (((leg %in% c('N','S')) && (dir %in% c('NB','SB'))) | ((leg %in% c('E','W')) && (dir %in% c('EB','WB')))){
        subset <- all_vehicles[all_vehicles$dir == dir 
                               & all_vehicles$leg == leg 
                               & all_vehicles$intersection_name == intersection_name,]
        dates <- data.frame(label = unique(all_vehicles$dt))
        dates$dow <- strftime(dates$lab, format = '%u')
        dates$wk <- strftime(dates$lab, format = '%W')
        dates$x = as.POSIXct('04:00', format = '%H:%M')
        dates$y = max(0.96 * max(subset$total_volume), 0.95)
        
        print(ggplot(data = subset, aes(x=time_bin, y=total_volume)) +
          geom_line(aes(color = ifelse(dow < 6, "weekday", "weekend")), size = 1) +
          scale_colour_brewer(type = 'qua', palette = "Set1", direction = -1) +
          scale_x_datetime(date_labels = "%H", date_breaks = "2 hours") +
          facet_grid(dow ~ wk) +
          geom_text(data = dates, aes(x,y-0.5,label=label)) +
          theme_light() + 
          guides(color = F) +
          theme(aspect.ratio = 1/2) +
          labs(title = paste0(intersection_name,": ", leg, " leg, ", dir, " direction")),
          width = 700, height = 1400)
      }
    }
  }
}

## [1] "1: Adelaide / Bathurst"

## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?

## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?

## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?

## [1] "2: Adelaide / Spadina"

## Warning in max(subset$total_volume): no non-missing arguments to max;
## returning -Inf

## Warning: Unknown or uninitialised column: 'PANEL'.

## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?

## [1] "3: Adelaide / Bay"

## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?

## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?

## [1] "4: Adelaide / Jarvis"

## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?

## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?

## [1] "5: Front / Bathurst"

## Warning in max(subset$total_volume): no non-missing arguments to max;
## returning -Inf

## Warning: Unknown or uninitialised column: 'PANEL'.

## Warning: no non-missing arguments to max; returning -Inf

## Warning: Unknown or uninitialised column: 'PANEL'.

## [1] "6: Front / Spadina"

## [1] "7: Front / Bay"

## [1] "8: Front / Jarvis"

## [1] "9: King / Strachan"

## [1] "10: King / Bathurst"

## [1] "11: King / Portland"

## [1] "12: King / Spadina"

## [1] "13: King / Peter"

## [1] "14: King / Simcoe"

## [1] "15: King / University"

## [1] "16: King / York"

## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?

## [1] "17: King / Bay"

## [1] "18: King / Yong"

## [1] "19: King / Church"

## [1] "20: King / Jarvis"

## [1] "21: King / Sherbourne"

## [1] "22: Queen / Bathurst"

## [1] "23: Queen / Spadina"

## [1] "24: Queen / Bay"

## [1] "25: Queen / Jarvis"

## [1] "26: Richmond / Bathurst"

## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?

## [1] "27: Richmond / Spadina"

## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?

## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?

## [1] "28: Richmond / Bay"

## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?

## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?

## [1] "29: Richmond / Jarvis"

## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?

## [1] "30: Wellington / Blue Jays"

## [1] "31: Wellington / Bay"

## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?

## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?